"""
写入Excel文件
"""


# from openpyxl import Workbook
#
# wb = Workbook()
#
# ws = wb.active
#
# ws['A1'] = 24
#
# ws.append([1,2,3])
#
# import datetime
# ws['A2'] = datetime.datetime.now()
#
# wb.save("sample1.xlsx")


# from openpyxl import Workbook
# wb = Workbook()
#
# # grab the active worksheet
# ws = wb.active
#
# # Data can be assigned directly to cells
# ws['A1'] = 42
#
# # Rows can also be appended
# ws.append([1, 2, 3])
#
# # Python types will automatically be converted
# import datetime
# ws['A2'] = datetime.datetime.now()
#
# # Save the file
# wb.save("sample.xlsx")


# from openpyxl import Workbook
# test_data = ["TCNo.","TEST_POINT","Automation","test_type","tc_owner","E_resDesc","E_resCode","Method","URL	page","tab","limit","mdrender"
# ]
# wb = Workbook()
# ws = wb.active
# for i in range(len(test_data)):
#     ws.cell(row = 1 ,column=i+1,value=test_data[i])
# wb.save("test.xlsx")


import os #导入模块
from openpyxl import load_workbook  #导入模块
from openpyxl.worksheet.worksheet import Worksheet #导入模块
# filepath = os.path.join(os.path.dirname(__file__),'test.xlsx') #找到文件路径
# #获取整个工作表
# workbook = load_workbook(filepath,data_only=True)
# #获取所有sheet
# print(workbook.sheetnames)
# worksheet:Worksheet = workbook[workbook.sheetnames[0]]
# print(worksheet.title)
# print(worksheet.values)
# print(worksheet.max_row)
# print(worksheet.max_column)
#  #提取出每一行有的数据内容
# for row in worksheet.iter_rows(min_row = 1,max_row = worksheet.max_row,
#                                  min_col = 1,max_col = worksheet.max_column,
#                                  values_only = True):
#     print(row)
#     for col in worksheet.iter_cols(min_row = 1,max_row = worksheet.max_row,
#                                  min_col = 1,max_col = worksheet.max_column,
#                                  values_only = True):
#         print(col)

# class FileHandeler:
#
#     def __init__(self):
#         self.root_dir = os.path.join(os.path.dirname(__file__),'../')
#
#     def write_excel(self):
#         pass
#
#     def read_excel(self,filepath,sheetname):
#         excelpath = os.path.join(self.root_dir,filepath)
#         workbook = load_workbook(excelpath)
#         worksheet:Worksheet = workbook[sheetname]
#         data = []
#         for row in worksheet.iter_rows(min_row=1,max_row=worksheet.max_row,
#                                        min_col= 1,max_col=worksheet.max_column,
#                                        values_only=True):
#             data.append(row)
#         return data
#
# if __name__ == '__main__':
#     f1 = FileHandeler()
#     data1 = f1.read_excel('commen/data.xlsx','主题首页')
#     print(data1)


"""
老师方法：主要存放数据出路的方法
"""
# import os
# from openpyxl import load_workbook
# from openpyxl.worksheet.worksheet import Worksheet

class FileHanlder:
    def __init__(self):#初始化函数
        self.root_dir = os.path.join(os.path.dirname(__file__),'../')

    def write_excel(self,filepath,sheetname,row,col,value):
        filepath = os.path.join(self.root_dir,filepath)
        wb = load_workbook(filepath)
        print(wb.sheetnames)
        ws:Worksheet = wb[sheetname]
        ws.cell(row,col,value)
        wb.save(filepath)

    def read_excel(self,filepath,sheetname):
        excelpath = os.path.join(self.root_dir,filepath)#拼接出所找文件路径
        workbook = load_workbook(excelpath) #载入文件
        worksheet:Worksheet = workbook[sheetname] #找到对应sheet
        data = []
        for row in worksheet.iter_rows(min_row=1,max_row=worksheet.max_row,
                                       min_col=1,max_col=worksheet.max_column,
                                       values_only=True):
            data.append(row)
        return data
    def write_csv(self,filepath,filedata):
        pass

    def read_csv(self,filepath):
        pass

if __name__ == '__main__':
    fl = FileHanlder()
    # data = fl.read_excel('commen/data.xlsx','主题首页')
    data2 = fl.write_excel('commen/data.xlsx','主题首页',2,2,'tamade好难')
    print(data2)